﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
namespace DAO
{
    public class DichVuDB
    {
        DataTable dt = new DataTable();
        DataProvider dp = new DataProvider();

        public DataTable dao_LayDSHoaDonTheoNgay(DateTime dtimebd, DateTime dtkt)
        {      
            string sql = "select dv.TenDV , Sum(cthd.SoLuong)as sl , dv.MaDV from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV,dv.MaDV order by sl desc ";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }

        //--------------------------------------------
        public DataTable dLayDSNuocUong()
        {
            string sql = "Select MaDV, TenDV, DonGia from DichVu where LoaiDV = 0";
            return dp.ExecuteQuery(sql);
        }
        public DataTable dTimNuocUongTheoTen(string tenThucUong)
        {
            string sql = "Select MaDV, TenDV, DonGia from DichVu where LoaiDV = 0 and TenDV like N'%" + tenThucUong + "%'";
            return dp.ExecuteQuery(sql);
        }

        public DataTable dLayDSBuffet(int loaiBuffet)
        {
            string sql = "select d.MaDV, d.TenDV, d.DonGia, h.HinhAnh from DichVu d , HinhAnhBuffet h where d.LoaiDV = " + loaiBuffet + " and d.MaDV = h.MaDV";
            return dp.ExecuteQuery(sql);
        }

        public string dLayBuffetCanBan(int madv)
        {
            string sql = "Select TenDV from DichVu where MaDV = " + madv + " and LoaiDV <> 0";
            return dp.ExecuteScalar_(sql);
        }

        public string dLayThucUongCanBan(int madv)
        {
            string sql = "Select TenDV from DichVu where MaDV = " + madv + "and LoaiDV = 0";
            return dp.ExecuteScalar_(sql);
        }

        public DataTable dLayDSBuffet()
        {
            string sql = "select MaDV, TenDV from DichVu where LoaiDV <> 0";
            return dp.ExecuteQuery(sql);
        }

        public string dLayGiaThucUong(int madv)
        {
            string sql = "Select DonGia from DichVu where MaDV = " + madv + "and LoaiDV = 0";
            return dp.ExecuteScalar_(sql);
        }
        //--------------------------------------------
        //------Cuong--------------------------------------
        public DataTable dLayDSDichVuSoPhanBanTheoNgay(DateTime dtimebd, DateTime dtkt)
        {
            string sql = "select dv.TenDV , Sum(cthd.SoLuong)as sl , dv.MaDV from CTThucDon cttd, CTHoaDon cthd, DichVu dv where dv.MaDV=cthd.MaDV and dv.MaDV=cttd.MaDV and NgayApDung>='" + dtimebd + "' and NgayApDung<='" + dtkt + "' group by dv.TenDV , cthd.MaDV,dv.MaDV order by sl desc ";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dLayDSDichVu()
        {
            string sql = "select * from DichVu";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dLayDSNVLChoMotDichVu(int MaDV)
        {
            string sql;
            sql = "select nvl.MaNL,nvl.TenNL,manl.SoLuongMotPhan ";
            sql = sql + "from DichVu dv,MonAnNguyenLieu manl,NguyenVatLieu nvl ";
            sql = sql + "where dv.MaDV=manl.MaDV ";
            sql = sql + "and manl.MaNL=nvl.MaNL ";
            sql = sql + "and dv.MaDV=" + MaDV;
            dt = dp.ExecuteQuery(sql);
            return dt;


        }
    }
}
